
Imports System.Data
Imports System.Data.SqlClient


Namespace Producto
Public Class Producto

public Function Listar(ProductoId as integer) as Entity.Producto.Producto

Dim strNameProcedure as string = "up_Pro_Producto_obtener"
Dim sqlReader As SqlDataReader = Nothing
Dim sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
dim objEnt as   entity.Producto.Producto
Try
            sqlCon.Open()
Dim sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
With sqlCmd
.CommandType = CommandType.StoredProcedure
                .Parameters.Add(New SqlParameter("@ProductoId", SqlDbType.Int)).Value =ProductoId
            End With
            sqlReader = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection)
If sqlReader.HasRows Then
While sqlReader.Read
 objEnt= new    entity.Producto.Producto
With objEnt
		.ProductoId = sqlReader("ProductoId").ToString
		.Catalogado = sqlReader("Catalogado").ToString
		.CatalogoId = sqlReader("CatalogoId").ToString
		.TipoId = sqlReader("TipoId").ToString
		.MarcaId = sqlReader("MarcaId").ToString
		.Nombre = sqlReader("Nombre").ToString
		.Codigo = sqlReader("Codigo").ToString
		.FormaSimpleId = sqlReader("FormaSimpleId").ToString
		.FormaCompuestaId = sqlReader("FormaCompuestaId").ToString
		.Concentracion = sqlReader("Concentracion").ToString
		.Presentacion = sqlReader("Presentacion").ToString
		.Fracciones = sqlReader("Fracciones").ToString
		.ProductoStock = sqlReader("ProductoStock").ToString
		.ProductoPrecioUnitario = sqlReader("ProductoPrecioUnitario").ToString
		.ProductoPrecioPromedio = sqlReader("ProductoPrecioPromedio").ToString
		.ProductoGenerico = sqlReader("ProductoGenerico").ToString
		.ProductoDescripcion = sqlReader("ProductoDescripcion").ToString
		.ProductoObservacion = sqlReader("ProductoObservacion").ToString
End With
  End While
  End IF
 Catch ex As Exception
Throw New Exception(ex.ToString)
Finally
 sqlReader.Dispose()
  sqlReader.Close()
  sqlReader = Nothing
  sqlCon.Dispose()
 sqlCon.Close()
 sqlCon = Nothing
 End Try
return objEnt

End Function


'***************************



        Public Function Listar(ByVal Opcion As String, _
          ByVal ProductoId As Integer, _
          ByVal Catalogado As Integer, _
          ByVal CatalogoId As Integer, _
          ByVal TipoId As Integer, _
          ByVal MarcaId As Integer, _
          ByVal Nombre As String, _
          ByVal Codigo As String, _
          ByVal FormaSimpleId As Integer, _
          ByVal FormaCompuestaId As Integer, _
          ByVal Concentracion As String, _
          ByVal Presentacion As String, _
          ByVal Fracciones As Integer, _
          ByVal ProductoStock As Integer, _
          ByVal ProductoPrecioUnitario As Double, _
          ByVal ProductoPrecioPromedio As Double, _
          ByVal ProductoGenerico As Integer, _
          ByVal ProductoDescripcion As String, _
          ByVal ProductoObservacion As String) _
        As Entity.Producto.ProductoCol

            Dim strNameProcedure As String = "up_Pro_Producto_listar"
            Dim objCol As Entity.Producto.ProductoCol
            Try
                Using conn As New SqlConnection(Configuracion.Connexion.Cnn)
                    Dim cmd As New SqlCommand(strNameProcedure, conn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 50)).Value = Opcion
                    cmd.Parameters.Add(New SqlParameter("@ProductoId", SqlDbType.Int)).Value = ProductoId
                    cmd.Parameters.Add(New SqlParameter("@Catalogado", SqlDbType.SmallInt)).Value = Catalogado
                    cmd.Parameters.Add(New SqlParameter("@CatalogoId", SqlDbType.Int)).Value = CatalogoId
                    cmd.Parameters.Add(New SqlParameter("@TipoId", SqlDbType.Int)).Value = TipoId
                    cmd.Parameters.Add(New SqlParameter("@MarcaId", SqlDbType.Int)).Value = MarcaId
                    cmd.Parameters.Add(New SqlParameter("@Nombre", SqlDbType.VarChar, 100)).Value = Nombre
                    cmd.Parameters.Add(New SqlParameter("@Codigo", SqlDbType.VarChar, 50)).Value = Codigo
                    cmd.Parameters.Add(New SqlParameter("@FormaSimpleId", SqlDbType.Int)).Value = FormaSimpleId
                    cmd.Parameters.Add(New SqlParameter("@FormaCompuestaId", SqlDbType.Int)).Value = FormaCompuestaId
                    cmd.Parameters.Add(New SqlParameter("@Concentracion", SqlDbType.VarChar, 150)).Value = Concentracion
                    cmd.Parameters.Add(New SqlParameter("@Presentacion", SqlDbType.VarChar, 150)).Value = Presentacion
                    cmd.Parameters.Add(New SqlParameter("@Fracciones", SqlDbType.Int)).Value = Fracciones
                    cmd.Parameters.Add(New SqlParameter("@ProductoStock", SqlDbType.Int)).Value = ProductoStock
                    cmd.Parameters.Add(New SqlParameter("@ProductoPrecioUnitario", SqlDbType.Decimal, 18)).Value = ProductoPrecioUnitario
                    cmd.Parameters.Add(New SqlParameter("@ProductoPrecioPromedio", SqlDbType.Decimal, 18)).Value = ProductoPrecioPromedio
                    cmd.Parameters.Add(New SqlParameter("@ProductoGenerico", SqlDbType.SmallInt)).Value = ProductoGenerico
                    cmd.Parameters.Add(New SqlParameter("@ProductoDescripcion", SqlDbType.VarChar, 250)).Value = ProductoDescripcion
                    cmd.Parameters.Add(New SqlParameter("@ProductoObservacion", SqlDbType.VarChar, 500)).Value = ProductoObservacion
                    conn.Open()
                    Using dr As SqlDataReader = cmd.ExecuteReader()
                        Dim iProductoId As Integer = dr.GetOrdinal("ProductoId")
                        Dim iCatalogado As Integer = dr.GetOrdinal("Catalogado")
                        Dim iCatalogoId As Integer = dr.GetOrdinal("CatalogoId")
                        Dim iTipoId As Integer = dr.GetOrdinal("TipoId")
                        Dim iMarcaId As Integer = dr.GetOrdinal("MarcaId")
                        Dim iNombre As Integer = dr.GetOrdinal("Nombre")
                        Dim iCodigo As Integer = dr.GetOrdinal("Codigo")
                        Dim iFormaSimpleId As Integer = dr.GetOrdinal("FormaSimpleId")
                        Dim iFormaCompuestaId As Integer = dr.GetOrdinal("FormaCompuestaId")
                        Dim iConcentracion As Integer = dr.GetOrdinal("Concentracion")
                        Dim iPresentacion As Integer = dr.GetOrdinal("Presentacion")
                        Dim iFracciones As Integer = dr.GetOrdinal("Fracciones")
                        Dim iProductoStock As Integer = dr.GetOrdinal("ProductoStock")
                        Dim iProductoPrecioUnitario As Integer = dr.GetOrdinal("ProductoPrecioUnitario")
                        Dim iProductoPrecioPromedio As Integer = dr.GetOrdinal("ProductoPrecioPromedio")
                        Dim iProductoGenerico As Integer = dr.GetOrdinal("ProductoGenerico")
                        Dim iProductoDescripcion As Integer = dr.GetOrdinal("ProductoDescripcion")
                        Dim iProductoObservacion As Integer = dr.GetOrdinal("ProductoObservacion")
                        Dim i_usu_cre As Integer = dr.GetOrdinal("_usu_cre")
                        Dim i_fec_cre As Integer = dr.GetOrdinal("_fec_cre")
                        Dim i_usu_mod As Integer = dr.GetOrdinal("_usu_mod")
                        Dim i_fec_mod As Integer = dr.GetOrdinal("_fec_mod")
                        Dim i_flg_eli As Integer = dr.GetOrdinal("_flg_eli")

                        Dim colCount As Integer = dr.FieldCount
                        Dim values As Object() = New Object(colCount - 1) {}

                        objCol = New Entity.Producto.ProductoCol
                        If dr.HasRows Then
                            While dr.Read()
                                Dim objEnt = New Entity.Producto.Producto
                                dr.GetValues(values)

                                objEnt.ProductoId = values(iProductoId)
                                objEnt.Catalogado = values(iCatalogado)
                                objEnt.CatalogoId = values(iCatalogoId)
                                objEnt.TipoId = values(iTipoId)
                                objEnt.MarcaId = values(iMarcaId)
                                objEnt.Nombre = values(iNombre)
                                objEnt.Codigo = values(iCodigo)
                                objEnt.FormaSimpleId = values(iFormaSimpleId)
                                objEnt.FormaCompuestaId = values(iFormaCompuestaId)
                                objEnt.Concentracion = values(iConcentracion)
                                objEnt.Presentacion = values(iPresentacion)
                                objEnt.Fracciones = values(iFracciones)
                                objEnt.ProductoStock = values(iProductoStock)
                                objEnt.ProductoPrecioUnitario = values(iProductoPrecioUnitario)
                                objEnt.ProductoPrecioPromedio = values(iProductoPrecioPromedio)
                                objEnt.ProductoGenerico = values(iProductoGenerico)
                                objEnt.ProductoDescripcion = values(iProductoDescripcion)
                                objEnt.ProductoObservacion = values(iProductoObservacion)
                                objCol.Add(objEnt)
                            End While
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.ToString)
            Finally
            End Try
            Return objCol

        End Function


'***************************


        Public Function Insert(ByVal opcion As String, _
          ByVal ProductoId As Integer, _
          ByVal Catalogado As Integer, _
          ByVal CatalogoId As Integer, _
          ByVal TipoId As Integer, _
          ByVal MarcaId As Integer, _
          ByVal Nombre As String, _
          ByVal Codigo As String, _
          ByVal FormaSimpleId As Integer, _
          ByVal FormaCompuestaId As Integer, _
          ByVal Concentracion As String, _
          ByVal Presentacion As String, _
          ByVal Fracciones As Integer, _
          ByVal ProductoStock As Integer, _
          ByVal ProductoPrecioUnitario As Double, _
          ByVal ProductoPrecioPromedio As Double, _
          ByVal ProductoGenerico As Integer, _
          ByVal ProductoDescripcion As String, _
          ByVal ProductoObservacion As String, _
        ByVal vUsuario As Integer) As Integer

            Dim strNameProcedure As String = "Up_Pro_Producto_Insertar"
            Try
                Dim intCodigo As Integer = -1
                Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
                    Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                        With sqlCmd
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                            .Parameters.Add(New SqlParameter("@ProductoId", SqlDbType.Int)).Value = ProductoId
                            .Parameters.Add(New SqlParameter("@Catalogado", SqlDbType.SmallInt)).Value = Catalogado
                            .Parameters.Add(New SqlParameter("@CatalogoId", SqlDbType.Int)).Value = CatalogoId
                            .Parameters.Add(New SqlParameter("@TipoId", SqlDbType.Int)).Value = TipoId
                            .Parameters.Add(New SqlParameter("@MarcaId", SqlDbType.Int)).Value = MarcaId
                            .Parameters.Add(New SqlParameter("@Nombre", SqlDbType.VarChar, 100)).Value = Nombre
                            .Parameters.Add(New SqlParameter("@Codigo", SqlDbType.VarChar, 50)).Value = Codigo
                            .Parameters.Add(New SqlParameter("@FormaSimpleId", SqlDbType.Int)).Value = FormaSimpleId
                            .Parameters.Add(New SqlParameter("@FormaCompuestaId", SqlDbType.Int)).Value = FormaCompuestaId
                            .Parameters.Add(New SqlParameter("@Concentracion", SqlDbType.VarChar, 150)).Value = Concentracion
                            .Parameters.Add(New SqlParameter("@Presentacion", SqlDbType.VarChar, 150)).Value = Presentacion
                            .Parameters.Add(New SqlParameter("@Fracciones", SqlDbType.Int)).Value = Fracciones
                            .Parameters.Add(New SqlParameter("@ProductoStock", SqlDbType.Int)).Value = ProductoStock
                            .Parameters.Add(New SqlParameter("@ProductoPrecioUnitario", SqlDbType.Decimal, 18)).Value = ProductoPrecioUnitario
                            .Parameters.Add(New SqlParameter("@ProductoPrecioPromedio", SqlDbType.Decimal, 18)).Value = ProductoPrecioPromedio
                            .Parameters.Add(New SqlParameter("@ProductoGenerico", SqlDbType.SmallInt)).Value = ProductoGenerico
                            .Parameters.Add(New SqlParameter("@ProductoDescripcion", SqlDbType.VarChar, 250)).Value = ProductoDescripcion
                            .Parameters.Add(New SqlParameter("@ProductoObservacion", SqlDbType.VarChar, 500)).Value = ProductoObservacion
                            .Parameters.Add(New SqlParameter("@_usuario", SqlDbType.Int)).Value = vUsuario
                            .Parameters.Add(New SqlParameter("@_fecha", SqlDbType.BigInt)).Value = Now.ToFileTime()
                            .Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
                            .Parameters("@variable_out").Direction = ParameterDirection.Output
                            sqlCon.Open()
                            .ExecuteNonQuery()
                            intCodigo = .Parameters("@variable_out").Value
                            Return intCodigo
                        End With
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message)
                Return -1
            End Try

        End Function


'***************************


        Public Function Update(ByVal opcion As String, _
          ByVal ProductoId As Integer, _
          ByVal Catalogado As Integer, _
          ByVal CatalogoId As Integer, _
          ByVal TipoId As Integer, _
          ByVal MarcaId As Integer, _
          ByVal Nombre As String, _
          ByVal Codigo As String, _
          ByVal FormaSimpleId As Integer, _
          ByVal FormaCompuestaId As Integer, _
          ByVal Concentracion As String, _
          ByVal Presentacion As String, _
          ByVal Fracciones As Integer, _
          ByVal ProductoStock As Integer, _
          ByVal ProductoPrecioUnitario As Double, _
          ByVal ProductoPrecioPromedio As Double, _
          ByVal ProductoGenerico As Integer, _
          ByVal ProductoDescripcion As String, _
          ByVal ProductoObservacion As String, _
        ByVal vUsuario As Integer) As Integer

            Dim strNameProcedure As String = "Up_Pro_Producto_Actualizar"
            Try
                Dim intCodigo As Integer = -1
                Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
                    Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
                        With sqlCmd
                            .CommandType = CommandType.StoredProcedure
                            .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                            .Parameters.Add(New SqlParameter("@ProductoId", SqlDbType.Int)).Value = ProductoId
                            .Parameters.Add(New SqlParameter("@Catalogado", SqlDbType.SmallInt)).Value = Catalogado
                            .Parameters.Add(New SqlParameter("@CatalogoId", SqlDbType.Int)).Value = CatalogoId
                            .Parameters.Add(New SqlParameter("@TipoId", SqlDbType.Int)).Value = TipoId
                            .Parameters.Add(New SqlParameter("@MarcaId", SqlDbType.Int)).Value = MarcaId
                            .Parameters.Add(New SqlParameter("@Nombre", SqlDbType.VarChar, 100)).Value = Nombre
                            .Parameters.Add(New SqlParameter("@Codigo", SqlDbType.VarChar, 50)).Value = Codigo
                            .Parameters.Add(New SqlParameter("@FormaSimpleId", SqlDbType.Int)).Value = FormaSimpleId
                            .Parameters.Add(New SqlParameter("@FormaCompuestaId", SqlDbType.Int)).Value = FormaCompuestaId
                            .Parameters.Add(New SqlParameter("@Concentracion", SqlDbType.VarChar, 150)).Value = Concentracion
                            .Parameters.Add(New SqlParameter("@Presentacion", SqlDbType.VarChar, 150)).Value = Presentacion
                            .Parameters.Add(New SqlParameter("@Fracciones", SqlDbType.Int)).Value = Fracciones
                            .Parameters.Add(New SqlParameter("@ProductoStock", SqlDbType.Int)).Value = ProductoStock
                            .Parameters.Add(New SqlParameter("@ProductoPrecioUnitario", SqlDbType.Decimal, 18)).Value = ProductoPrecioUnitario
                            .Parameters.Add(New SqlParameter("@ProductoPrecioPromedio", SqlDbType.Decimal, 18)).Value = ProductoPrecioPromedio
                            .Parameters.Add(New SqlParameter("@ProductoGenerico", SqlDbType.SmallInt)).Value = ProductoGenerico
                            .Parameters.Add(New SqlParameter("@ProductoDescripcion", SqlDbType.VarChar, 250)).Value = ProductoDescripcion
                            .Parameters.Add(New SqlParameter("@ProductoObservacion", SqlDbType.VarChar, 500)).Value = ProductoObservacion
                            .Parameters.Add(New SqlParameter("@_usuario", SqlDbType.Int)).Value = vUsuario
                            .Parameters.Add(New SqlParameter("@_fecha", SqlDbType.BigInt)).Value = Now.ToFileTime()
                            .Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
                            .Parameters("@variable_out").Direction = ParameterDirection.Output
                            sqlCon.Open()
                            .ExecuteNonQuery()
                            intCodigo = .Parameters("@variable_out").Value
                            Return intCodigo
                        End With
                    End Using
                End Using
            Catch ex As Exception
                Throw New Exception(ex.Message)
                Return -1
            End Try

        End Function


'***************************


Public Shared Function Eliminar(ByVal opcion As String, ProductoId as integer, vusuario as integer) as integer

 dim strNameProcedure as string ="Up_Pro_Producto_Eliminar"
 Try
Dim intCodigo As Integer = -1
 Using sqlCon As SqlConnection = New SqlConnection(Configuracion.Connexion.Cnn)
Using sqlCmd As SqlCommand = New SqlCommand(strNameProcedure, sqlCon)
With sqlCmd
 .CommandType = CommandType.StoredProcedure
 .Parameters.Add(New SqlParameter("@opcion", SqlDbType.VarChar, 120)).Value = opcion
                .Parameters.Add(New SqlParameter("@ProductoId", SqlDbType.Int)).Value =ProductoId
 .Parameters.Add(New SqlParameter("@_usuario", SqlDbType.Int)).Value = vUsuario
 .Parameters.Add(New SqlParameter("@_fecha", SqlDbType.BigInt)).Value = now.tofiletime()
.Parameters.Add(New SqlParameter("@variable_out", SqlDbType.Int))
 .Parameters("@variable_out").Direction = ParameterDirection.Output
 sqlCon.Open()
 .ExecuteNonQuery()
 intCodigo = .Parameters("@variable_out").Value
  Return intCodigo
  End With
 End Using
 End Using
 Catch ex As Exception
  Throw New Exception(ex.Message)
 Return -1
 End Try

End Function


'***************************



End Class



end namespace

